package model.dao.registerOrder;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import log.MyLogger;
import model.user.ServiceOrderRequest;

import db.dbconnection;

public class SearchOrderRequest {

	String sql = "" ;
	 db.dbconnection dbconnection ;
	 java.sql.Connection myConnection ;
	 ResultSet rs ;
	 Statement st ;
	 ArrayList<ServiceOrderRequest> sorList=new ArrayList<ServiceOrderRequest>();
	 ServiceOrderRequest so_request;

	 
	public ArrayList<ServiceOrderRequest> getAllRequests() throws NumberFormatException{
		try {
		    dbconnection = new dbconnection();
		    myConnection = dbconnection.getConnection();
		    st = myConnection.createStatement();
		    sql = "SELECT SR.service_request, SO.service_order, P.first_name,P.last_name, SR.service_desc_by_customer, " +
		    		"SR.service_desc_by_employee FROM service_request SR " +
		    		"LEFT JOIN customer C ON SR.customer_fk = C.customer INNER JOIN person P ON C.subject_fk = P.person " +
		    		"LEFT JOIN service_order SO ON SR.service_request = SO.service_request_fk  " +
		    		"WHERE C.subject_type_fk=1";
			rs = st.executeQuery(sql);
			sorList=new ArrayList<ServiceOrderRequest>();
			String testString;
		    while(rs.next()){
		    	so_request=new ServiceOrderRequest();
		    	so_request.setServiceRequest(Integer.parseInt(rs.getString("service_request")));
		    	testString=rs.getString("service_order");
		    	if(testString!=null && !testString.equalsIgnoreCase(""))
		    		so_request.setServiceOrder(Integer.parseInt(testString));
		    	so_request.setName(rs.getString("first_name")+ " "+ rs.getString("last_name"));
		    	so_request.setCustomer_desc(rs.getString("service_desc_by_customer"));
		    	so_request.setEmployee_desc(rs.getString("service_desc_by_employee"));
		    	sorList.add(so_request);
		    }
		    
		    sql = "SELECT SR.service_request, SO.service_order, E.full_name, SR.service_desc_by_customer, SR.service_desc_by_employee FROM service_request SR " +
		    		"LEFT JOIN Customer C ON SR.customer_fk = C.customer INNER JOIN Enterprise E ON C.subject_fk = E.enterprise " +
		    		"LEFT JOIN service_order SO ON SR.service_request = SO.service_request_fk  " +
		    		"WHERE C.subject_type_fk=2";
			rs = st.executeQuery(sql);
			
		    while(rs.next()){
		    	so_request=new ServiceOrderRequest();
		    	so_request.setServiceRequest(Integer.parseInt(rs.getString("service_request")));
		    	testString=rs.getString("service_order");
		    	if(testString!=null && !testString.equalsIgnoreCase(""))
		    		so_request.setServiceOrder(Integer.parseInt(testString));
		    	so_request.setName(rs.getString("full_name"));
		    	so_request.setCustomer_desc(rs.getString("service_desc_by_customer"));
		    	so_request.setEmployee_desc(rs.getString("service_desc_by_employee"));
		    	sorList.add(so_request);
		    }
	    	
		    this.dbconnection.close();
		    return sorList;
		} catch (SQLException e) {
			// logimine
//			MyLogger.Log("SearchOrderRequestImpl: getAllRequests ", e.getMessage());
			return null;
			
		}

		
	}
}
